﻿SELECT 
	CASE 
	WHEN A.UNIT_NUM IS NULL OR A.UNIT_NUM = '' THEN A.STREET_NUM||' '||A.STREET_NAME 
	ELSE A.STREET_NUM||' '||A.STREET_NAME||', '||A.UNIT_NUM 
	END ADDR, 
	CASE 
	WHEN A.BAN IS NOT NULL THEN '#47CAFF' 
	WHEN B.ELU_ID IS NOT NULL THEN '#a4f368' 
	ELSE '#FFFFFF' 
	END COLOR, 
	TRIM(
		CAST(
			CAST(
				COALESCE(PMASTER_ADDR_ID,0) 
			AS FORMAT 'ZZZZZZZZZ9') 
		AS CHAR(10))
		)
	||':'||COALESCE(B.ELU_ID,'') ACCTS, 
	COALESCE(A.BAN,'') BAN 
FROM 
	EDWEN.ACC_LIST 
	A 
	LEFT OUTER JOIN 
	SUNRISE_NB.SE_GREENS 
	B 
	ON A.ELU_ID=B.ELU_ID 
WHERE 
	A.TRAC_ID='{0}' 
ORDER BY 1